package cs342.uwosh.group1.mycontacts;

import android.database.sqlite.SQLiteOpenHelper;
import java.util.*;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlConnect extends SQLiteOpenHelper{
	
	public SqlConnect(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	/*
	 * Database version number
	 */
	private static final int DATABASE_VERSION = 1;
	/*
	 * Name of the Database
	 */
	private static final String DATABASE_NAME = "MyContacts";
	/*
	 * Name of the contacts table.
	 */
	private static final String TABLE_CONTACTS = "contacts";
	/*
	 * Contact database columns.
	 */
	private static final String ID_KEY = "_id";
	private static final String NAME_KEY = "name";
	private static final String ADDRESS_KEY = "address";
	private static final String CELLNUMBER_KEY = "cellNumber";
	private static final String ALTNUMBER_KEY = "altNumber";
	private static final String EMAIL_KEY = "email";
	private static final String COMMENTS_KEY = "comments";
	private static final String CATEGORY_KEY = "category";
	
	private static final String TABLE_CATEGORIES = "catagories";
	private static final String CID_KEY = "_cid";
	private static final String CATEGORY_NAME_KEY = "cataroy_name";
	
	
	
	
	@Override
	public void onCreate(SQLiteDatabase database) {
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
				+ ID_KEY + " INTEGER PRIMARY KEY,"
				+ NAME_KEY + " TEXT,"
				+ ADDRESS_KEY + " TEXT,"
				+ CELLNUMBER_KEY + " TEXT,"
				+ ALTNUMBER_KEY + " TEXT,"
				+ EMAIL_KEY + " TEXT,"
				+ COMMENTS_KEY + " TEXT,"
				+ CATEGORY_KEY + " INTEGER,"
				+ " FOREIGN KEY ("+CATEGORY_KEY+") REFERENCES "+TABLE_CATEGORIES+" ("+CID_KEY+ "))";
		database.execSQL(CREATE_CONTACTS_TABLE);
		
		String CREATE_CATAGORIES_TABLE = "CREATE TABLE " + TABLE_CATEGORIES + "("
				+ CID_KEY + " INTEGER PRIMARY KEY,"
				+ CATEGORY_NAME_KEY + " TEXT" + ")";
		database.execSQL(CREATE_CATAGORIES_TABLE);
		
		
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldDB, int newDB) {
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
		this.onCreate(database);
	}
	
	public void dropTables()
	{
		SQLiteDatabase database = this.getWritableDatabase();
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
		this.onCreate(database);
	}
	/**
	 * Called to create a database entry for the passed in contact.
	 * @param contact The contact to be created in the database.
	 */
	public void createContact(Contact contact)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues colValues = new ContentValues();	
		colValues.put(NAME_KEY, contact.getName());
		colValues.put(ADDRESS_KEY, contact.getAddress());
		colValues.put(CELLNUMBER_KEY, contact.getCellNumber());
		colValues.put(ALTNUMBER_KEY, contact.getAltNumber());
		colValues.put(EMAIL_KEY, contact.getEmail());
		colValues.put(COMMENTS_KEY, contact.getComments());
		colValues.put(CATEGORY_KEY, contact.getCategoryID());
		database.insert(TABLE_CONTACTS, null, colValues);
		database.close();
	}
	/**
	 * Called to create a datsabase entry for the passed in category.
	 * @param catagory The category to be created.
	 */
	public void createCategory(Category category)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		ContentValues colValues = new ContentValues();	
		colValues.put(CATEGORY_NAME_KEY, category.getCategoryName());

		database.insert(TABLE_CATEGORIES, null, colValues);
		database.close();
	}
	
	public Contact getContact(int id)
	{
		SQLiteDatabase database = this.getReadableDatabase();
		
		Cursor cursor = database.query(TABLE_CONTACTS,
				new String[] {NAME_KEY, ADDRESS_KEY, CELLNUMBER_KEY, ALTNUMBER_KEY, EMAIL_KEY, COMMENTS_KEY, CATEGORY_KEY},
				ID_KEY + "=?",
				new String[] {String.valueOf(id)},
				null, null, null, null);
		if (cursor != null)
		{
			cursor.moveToFirst();
		}
		Contact temp = new Contact(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1),
				cursor.getString(2),
				cursor.getString(3),
				cursor.getString(4),
				cursor.getString(5),
				cursor.getString(6),
				Integer.parseInt(cursor.getString(7))
				);
		cursor.close();
		return temp;
	}
	
	public Category getCategory(int cid)
	{
		SQLiteDatabase database = this.getReadableDatabase();
		
		
		String query = "SELECT * FROM " + TABLE_CATEGORIES + " WHERE _cid=" + cid;
        Cursor cursor = database.rawQuery(query, null);
		if (cursor != null)
		{
			cursor.moveToFirst();
		}
		Category temp = new Category(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1)
				);
		cursor.close();
		return temp;
	}
	
	public Contact getContact(String name)
	{
		SQLiteDatabase database = this.getReadableDatabase();
		
		String statement = "SELECT * FROM " + TABLE_CONTACTS + " WHERE name=\"" + name + "\"";
				
		Cursor cursor = database.rawQuery(statement, null);
		if (cursor != null)
		{
			cursor.moveToFirst();
		}
		Contact temp = new Contact(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1),
				cursor.getString(2),
				cursor.getString(3),
				cursor.getString(4),
				cursor.getString(5),
				cursor.getString(6)
				);
		cursor.close();
		return temp;
	}
	
	
	
	public List<Contact> getAll()
	{
		List<Contact> contactsList = new ArrayList<Contact>();
		String query = "SELECT  * FROM " + TABLE_CONTACTS + " ORDER BY " + NAME_KEY + " COLLATE NOCASE ASC";
		SQLiteDatabase database = this.getWritableDatabase();
		Cursor cursor = database.rawQuery(query, null);
		if (cursor.moveToFirst())
		{
			do
			{
				Contact contact = new Contact();
				contact.setId(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.setAddress(cursor.getString(2));
				contact.setCellNumber(cursor.getString(3));
				contact.setAltNumber(cursor.getString(4));
				contact.setEmail(cursor.getString(5));
				contact.setComments(cursor.getString(6));
				contact.setCategoryID(Integer.parseInt(cursor.getString(7)));
				contactsList.add(contact);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return contactsList;
	}
	
	public List<Contact> getContactsFromCategory(int cid)
	{
		List<Contact> contactsList = new ArrayList<Contact>();
		String query = "SELECT  * FROM " + TABLE_CONTACTS + " WHERE category="+cid;
		SQLiteDatabase database = this.getWritableDatabase();
		Cursor cursor = database.rawQuery(query, null);
		if (cursor.moveToFirst())
		{
			do
			{
				Contact contact = new Contact();
				contact.setId(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.setAddress(cursor.getString(2));
				contact.setCellNumber(cursor.getString(3));
				contact.setAltNumber(cursor.getString(4));
				contact.setEmail(cursor.getString(5));
				contact.setComments(cursor.getString(6));
				contact.setCategoryID(Integer.parseInt(cursor.getString(7)));
				contactsList.add(contact);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return contactsList;
	}
	
	public List<Category> getAllCategories()
	{
		ArrayList<Category> categoriesList = new ArrayList<Category>();
		String query = "SELECT * FROM " + TABLE_CATEGORIES;
		SQLiteDatabase database = this.getWritableDatabase();
		Cursor cursor = database.rawQuery(query, null);
		if(cursor.moveToFirst())
		{
			do
			{
				Category category = new Category();
				category.setCid(Integer.parseInt(cursor.getString(0)));
				category.setCatagoryName(cursor.getString(1));
				categoriesList.add(category);
			}while(cursor.moveToNext());
				
		}
		cursor.close();
		return categoriesList;
	}
	
	public int contactCount()
	{
		String query = "SELECT * FROM " + TABLE_CONTACTS;
		SQLiteDatabase database = this.getReadableDatabase();
		Cursor cursor = database.rawQuery(query, null);
		int count = cursor.getCount();
		cursor.close();
		
		return count;
	}
	
	public int categoryCount()
	{
		String query = "SELECT * FROM " + TABLE_CATEGORIES;
		SQLiteDatabase database = this.getReadableDatabase();
		Cursor cursor = database.rawQuery(query, null);
		int count = cursor.getCount();
		cursor.close();
		
		return count;
	}
	
	public int updateContact(Contact contact)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		
		ContentValues colValues = new ContentValues();
		colValues.put(NAME_KEY, contact.getName());
		colValues.put(ADDRESS_KEY, contact.getAddress());
		colValues.put(CELLNUMBER_KEY, contact.getCellNumber());
		colValues.put(ALTNUMBER_KEY, contact.getAltNumber());
		colValues.put(EMAIL_KEY, contact.getEmail());
		colValues.put(COMMENTS_KEY, contact.getComments());
		colValues.put(CATEGORY_KEY, contact.getCategoryID());
		return database.update(
				TABLE_CONTACTS,
				colValues,
				ID_KEY + " = ?",
				new String[] {String.valueOf(contact.getId())});
	}
	
	public int updateCategory(Category category)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		
		ContentValues colValues = new ContentValues();
		colValues.put(CATEGORY_NAME_KEY, category.getCategoryName());

		
		return database.update(
				TABLE_CATEGORIES,
				colValues,
				CID_KEY + " = ?",
				new String[] {String.valueOf(category.getCid())});
	}
	
	public void deleteContact(Contact contact)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		database.delete(
				TABLE_CONTACTS,
				ID_KEY+"="+contact.getId(),
				null);
		database.close();
	}
	
	public void deleteCategory(Category category)
	{
		SQLiteDatabase database = this.getWritableDatabase();
		database.delete(
				TABLE_CATEGORIES,
				CID_KEY+"="+category.getCid(),
				null);
		database.close();
	}
	
}
